
WITH DailyData AS (
    SELECT 
        CAST(B.F_STU_BES_DATE AS DATE) AS AuditDate,
        COUNT(B.F_STU_BES_GUID) AS RegistCount,
        COUNT(DISTINCT CASE WHEN CAST(S.F_STU_DATE AS DATE) = CAST(B.F_STU_BES_DATE AS DATE) THEN S.F_STU_BES_GUID END) AS CompletedCount
    FROM 
        BESPOKE.dbo.T_BESPOKE B
    LEFT JOIN 
        PACS.dbo.T_STUDY_BASIC S ON B.F_STU_BES_GUID = S.F_STU_BES_GUID
	Where B.F_STU_BES_DATE >=DATEADD(YEAR,-1,GETDATE())
    GROUP BY 
        CAST(B.F_STU_BES_DATE AS DATE)
)
SELECT 
    AuditDate,
    RegistCount,
    CompletedCount,
    CASE 
        WHEN RegistCount - CompletedCount < 0 THEN 0 
        ELSE RegistCount - CompletedCount 
    END AS UnDoCount,
    CASE 
        WHEN RegistCount = 0 THEN 0.00 
        ELSE ROUND(CAST(CompletedCount AS FLOAT) / RegistCount * 100, 2)
    END AS CompletedPec,
    CASE 
        WHEN RegistCount = 0 THEN 0.00 
        ELSE ROUND(100.0 - (CAST(CompletedCount AS FLOAT) / RegistCount * 100), 2)
    END AS UnDoRate
FROM 
    DailyData;

use [BESPOKE]
Select F_STU_BES_DATE, from [dbo].[T_BESPOKE]